package com.zx.ziroom;

import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbutils.QueryRunner;

import com.zx.common.C3P0Utils;

public class RoomDao {
	
	public void insert(Room room){
		Connection connection = C3P0Utils.getConnection();
		
		QueryRunner query = new QueryRunner();
		
		String sql = "insert into ziroom values(?,?,?,?,?,?,?,?,?,?)";
		try {
			query.update(connection, sql, null, room.getRoomName(), room.getRoomPrice(), room.getCity(), 
					room.getRoomUrl(), room.getArea(), room.getOrientation(), room.getRoomType(), room.getPriceType(), room.getFloor());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			C3P0Utils.close(connection, null, null);
		}
	}
	
	public boolean createTable() throws SQLException {
		String tableName = "ZIROOM"; 
		String createSql = "CREATE TABLE `ZIROOM` ( "+
				  "`id` int(11) NOT NULL AUTO_INCREMENT, "+
				  "`room_name` varchar(64) COLLATE utf8_bin DEFAULT NULL, "+
				  "`room_price` varchar(64) COLLATE utf8_bin DEFAULT NULL, "+
				  "`city` varchar(32) COLLATE utf8_bin DEFAULT NULL, "+
				  "`room_url` varchar(256) COLLATE utf8_bin DEFAULT NULL, "+
				  "`area` varchar(32) COLLATE utf8_bin DEFAULT NULL, "+
				  "`orientation` varchar(32) COLLATE utf8_bin DEFAULT NULL, "+
				  "`room_type` varchar(32) COLLATE utf8_bin DEFAULT NULL, "+
				  "`price_type` varchar(32) COLLATE utf8_bin DEFAULT NULL, "+
				  "`floor` varchar(32) COLLATE utf8_bin DEFAULT NULL, "+
				  "PRIMARY KEY (`id`) "+
				") ENGINE=InnoDB AUTO_INCREMENT=4918 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ";
		try {
			boolean tableExist = C3P0Utils.isTableExist(tableName);
			if(tableExist) {
				C3P0Utils.dropTable(tableName);
			} 
			C3P0Utils.createTable(createSql);
			return true;
		} catch (SQLException e) {
			throw e;
		}
	}
	
}
